1 Imports System.Data.SqlClient
2 Public Class frmCategory
3
4 Private Sub btnClose_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnClose.Click
5 Me.Close()
6 End Sub
7 Sub Reset()
8 txtcategory.Text = ""
9 btnSave.Enabled = True
10 btnDelete.Enabled = False
11 btnUpdate.Enabled = False
12 txtcategory.Focus()
13 End Sub
14 Private Sub btnNew_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnNew.Click
15 Reset()
16 End Sub
17
18 Private Sub btnSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSave.Click
19 If txtcategory.Text = "" Then
20 MessageBox.Show("Please enter category", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
21 txtcategory.Focus()
22 Return
23 End If
24
25 Try
26 con = New SqlConnection(cs)
27 con.Open()
28 Dim ct As String = "select categoryname from category where categoryname=@d1"
29 cmd = New SqlCommand(ct)
30 cmd.Parameters.AddWithValue("@d1", txtcategory.Text)
31 cmd.Connection = con
32 rdr = cmd.ExecuteReader()
33
34 If rdr.Read() Then
35 MessageBox.Show("Category Already Exists", "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
36 txtcategory.Text = ""
37 txtcategory.Focus()
38 If (rdr IsNot Nothing) Then
39 rdr.Close()
40 End If
41 Return
42 End If
43
44 con = New SqlConnection(cs)
45 con.Open()
46
47 Dim cb As String = "insert into category(categoryName) VALUES (@d1)"
48 cmd = New SqlCommand(cb)
49 cmd.Parameters.AddWithValue("@d1", txtcategory.Text)
50 cmd.Connection = con
51 cmd.ExecuteReader()
52 con.Close()
53 LogFunc(lblUser.Text, "added the new category '" & txtcategory.Text & "'")
54 MessageBox.Show("Successfully Saved", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
55 btnSave.Enabled = False
56 Getdata()
57 Catch ex As Exception
58 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
59 End Try
60 End Sub
61
62 Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
63 If txtcategory.Text = "" Then
64 MessageBox.Show("Please enter category", "", MessageBoxButtons.OK, MessageBoxIcon.Warning)
65 txtcategory.Focus()
66 Return
67 End If
68
69 Try
70
71 con = New SqlConnection(cs)
72 con.Open()
73
74 Dim cb As String = "Update category set categoryname=@d1 where categoryname=@d2"
75 cmd = New SqlCommand(cb)
76 cmd.Connection = con
77 cmd.Parameters.AddWithValue("@d1", txtcategory.Text)
78 cmd.Parameters.AddWithValue("@d2", txtcategoryName.Text)
79 cmd.ExecuteReader()
80 LogFunc(lblUser.Text, "updated the category '" & txtcategory.Text & "'")
81 MessageBox.Show("Successfully updated", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
82 btnUpdate.Enabled = False
83 Getdata()
84 Catch ex As Exception
85 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
86 End Try
87 End Sub
88 Private Sub DeleteRecord()
89
90 Try
91 Dim RowsAffected As Integer = 0
92 con = New SqlConnection(cs)
93 con.Open()
94 Dim cl As String = "select CategoryName from Category,SubCategory where Category.CategoryName=SubCategory.Category and CategoryName=@d1"
95 cmd = New SqlCommand(cl)
96 cmd.Connection = con
97 cmd.Parameters.AddWithValue("@d1", txtCategoryName.Text)
98 rdr = cmd.ExecuteReader()
99 If rdr.Read Then
100 MessageBox.Show("Unable to delete..Already in use in Sub Category Entry", "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
101 If Not rdr Is Nothing Then
102 rdr.Close()
103 End If
104 Exit Sub
105 End If
106 con = New SqlConnection(cs)
107 con.Open()
108 Dim cq As String = "delete from category where categoryname=@d1"
109 cmd = New SqlCommand(cq)
110 cmd.Parameters.AddWithValue("@d1", txtcategoryName.Text)
111 cmd.Connection = con
112 RowsAffected = cmd.ExecuteNonQuery()
113 If RowsAffected > 0 Then
114 LogFunc(lblUser.Text, "deleted the category '" & txtcategory.Text & "'")
115 MessageBox.Show("Successfully deleted", "Record", MessageBoxButtons.OK, MessageBoxIcon.Information)
116 Getdata()
117 Reset()
118 Else
119 MessageBox.Show("No Record found", "Sorry", MessageBoxButtons.OK, MessageBoxIcon.Information)
120 Reset()
121 End If
122 If con.State = ConnectionState.Open Then
123 con.Close()
124
125 End If
126 Catch ex As Exception
127 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
128 End Try
129 End Sub
130 Private Sub btnDelete_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnDelete.Click
131 Try
132 If MessageBox.Show("Do you really want to delete this record?", "Confirmation", MessageBoxButtons.YesNo, MessageBoxIcon.Warning) = Windows.Forms.DialogResult.Yes Then
133 DeleteRecord()
134 End If
135 Catch ex As Exception
136 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
137 End Try
138 End Sub
139
140 Private Sub dgw_MouseClick(ByVal sender As Object, ByVal e As System.Windows.Forms.MouseEventArgs) Handles dgw.MouseClick
141 Try
142 If dgw.Rows.Count > 0 Then
143 Dim dr As DataGridViewRow = dgw.SelectedRows(0)
144 txtCategoryName.Text = dr.Cells(0).Value.ToString()
145 txtCategory.Text = dr.Cells(0).Value.ToString()
146 btnUpdate.Enabled = True
147 btnDelete.Enabled = True
148 btnSave.Enabled = False
149 End If
150 Catch ex As Exception
151 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
152 End Try
153 End Sub
154
155 Private Sub dgw_RowPostPaint(ByVal sender As Object, ByVal e As System.Windows.Forms.DataGridViewRowPostPaintEventArgs) Handles dgw.RowPostPaint
156 Dim strRowNumber As String = (e.RowIndex + 1).ToString()
157 Dim size As SizeF = e.Graphics.MeasureString(strRowNumber, Me.Font)
158 If dgw.RowHeadersWidth < Convert.ToInt32((size.Width + 20)) Then
159 dgw.RowHeadersWidth = Convert.ToInt32((size.Width + 20))
160 End If
161 Dim b As Brush = SystemBrushes.ControlText
162 e.Graphics.DrawString(strRowNumber, Me.Font, b, e.RowBounds.Location.X + 15, e.RowBounds.Location.Y + ((e.RowBounds.Height - size.Height) / 2))
163
164 End Sub
165 Public Sub Getdata()
166 Try
167 con = New SqlConnection(cs)
168 con.Open()
169 cmd = New SqlCommand("SELECT RTRIM(categoryname) from category order by categoryname", con)
170 rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection)
171 dgw.Rows.Clear()
172 While (rdr.Read() = True)
173 dgw.Rows.Add(rdr(0))
174 End While
175 con.Close()
176 Catch ex As Exception
177 MessageBox.Show(ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
178 End Try
179 End Sub
180
181
182 Private Sub frmcategory_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
183 Getdata()
184 End Sub
185 End Class
